package mww.javabean.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Vector;

import mww.common.*;
import mww.javabean.entity.Student;

public class StudentDaoImpl implements StudentDao {
	// 添加一个学生
	public void addStudent(Student student) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionFactory.getConnection();// 调用ConnectionFactory组件的getConnection()方法来获得数据库连接
			String sql = "insert into Student(studentNo, studentName, password, studentClass,studentMajor) values(?,?,?,?,?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, student.getStudentNo());
			pstmt.setString(2, student.getStudentName());
			pstmt.setString(3, student.getPassword());
			pstmt.setString(4, student.getStudentClass());
			pstmt.setString(5, student.getStudentMajor());
			pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);// 调用ResourceClose组件的close方法来关闭数据库资源
		}
	}

	// 根据学生学号删除一个学生
	public void delStudent(String studentNo) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "delete from Student where studentNo=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, studentNo);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
	}

	// 修改一个学生的信息
	public void updateStudent(Student student) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "update Student set studentNo=?,studentName=?,password=?,studentClass=?,studentMajor=?,sumCredit=?,sumCreditGPA=?,avgCreditGPA=?,majorRank=?,classRank=? where studentNo=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, student.getStudentNo());
			pstmt.setString(2, student.getStudentName());
			pstmt.setString(3, student.getPassword());
			pstmt.setString(4, student.getStudentClass());
			pstmt.setString(5, student.getStudentMajor());
			pstmt.setFloat(6, student.getSumCredit());
			pstmt.setFloat(7, student.getSumCreditGPA());
			pstmt.setFloat(8, student.getAvgCreditGPA());
			pstmt.setInt(9, student.getMajorRank());
			pstmt.setInt(10, student.getClassRank());
			pstmt.setString(11, student.getStudentNo());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
	}

	// 列表显示所有学生列表--分页
	public Map findAllStudent(Integer curPage) {
		Student student = null;
		ArrayList list = new ArrayList();// 声明一个List对象，用于存放所要显示学生对象
		Connection conn = null;
		Statement pstmt = null;
		ResultSet rs = null;
		ResultSet r = null;
		Map map = null;
		Page pa = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "select * from Student order by studentNo";
			pstmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			rs = pstmt.executeQuery(sql);
			pa = new Page();// 声明分页类对象
			pa.setPageSize(5);// 设置每页所要显示记录个数
			pa.setPageCount(rs);// 根据结果集设置所有要显示的记录总数
			pa.setCurPage(curPage);// 设置所要显示页
			r = pa.setRs(rs);// 设置要显示的结果集，此时记录指针处于第一条记录上
			r.previous();// 记录指针前移，使之处于第一条记录之前的位置
			// 利用循环操作将查询到的结果集放入List对象中
			for (int i = 0; i < pa.getPageSize(); i++) {
				if (r.next()) {
					// 将学生记录转化为学生对象
					student = new Student();
					student.setStudentNo(rs.getString(1));
					student.setStudentName(rs.getString(2));
					student.setStudentClass(rs.getString(3));
					student.setStudentMajor(rs.getString(4));
					student.setPassword(rs.getString(5));
					student.setSumCredit(rs.getFloat(6));
					student.setSumCreditGPA(rs.getFloat(7));
					student.setAvgCreditGPA(rs.getFloat(8));
					student.setMajorRank(rs.getInt(9));
					student.setClassRank(rs.getInt(10));
					list.add(student);
				} else {
					break;
				}
			}
			map = new HashMap();// 声明一个Map对象
			map.put("list", list);// 将list对象放入Map对象
			map.put("pa", pa);// 将Page对象放入Map对象
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
			ResourceClose.close(r, null, null);
		}
		return map;
	}

	@Override
	public List findAllStudent() {
		Student student = null;
		ArrayList list = new ArrayList();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select * from Student order by studentNo";
		try {
			conn = ConnectionFactory.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				// 将学生记录转化为学生对象
				student = new Student();
				student.setStudentNo(rs.getString(1));
				student.setStudentName(rs.getString(2));
				student.setStudentClass(rs.getString(3));
				student.setStudentMajor(rs.getString(4));
				student.setPassword(rs.getString(5));
				student.setSumCredit(rs.getFloat(6));
				student.setSumCreditGPA(rs.getFloat(7));
				student.setAvgCreditGPA(rs.getFloat(8));
				student.setMajorRank(rs.getInt(9));
				student.setClassRank(rs.getInt(10));
				list.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
		return list;
	}

	@Override
	public Student findStudentByStudentNo(String studentNo) {
		Student student = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "select * from student where studentNo=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, studentNo);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				student = new Student();
				student.setStudentNo(rs.getString(1));
				student.setStudentName(rs.getString(2));
				student.setStudentClass(rs.getString(3));
				student.setStudentMajor(rs.getString(4));
				student.setPassword(rs.getString(5));
				student.setSumCredit(rs.getFloat(6));
				student.setSumCreditGPA(rs.getFloat(7));
				student.setAvgCreditGPA(rs.getFloat(8));
				student.setMajorRank(rs.getInt(9));
				student.setClassRank(rs.getInt(10));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
		return student;
	}

	@Override
	public void updateStudentPwd(String studentNo, String newPassword) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "update Student set password=? where studentNo=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, newPassword);
			pstmt.setString(2, studentNo);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
	}

	@Override
	public Map findAllStudentByMostCon(Student condition, Integer curPage) {
		Student student = null;
		ArrayList list = new ArrayList();
		Connection conn = null;
		Statement pstmt = null;
		ResultSet rs = null;

		ResultSet r = null;
		Map map = null;
		Page pa = null;
		String studentNo = condition.getStudentNo();
		String studentName = condition.getStudentName();
		String studentClass = condition.getStudentClass();
		String studentMajor = condition.getStudentMajor();
		System.out.println("studentNo="+studentNo+",studentClass="+studentClass+",studentMajor="+studentMajor);
		// 构造多条件查询的SQL语句
		String sql = "select * from Student where 1=1 ";
		// 模糊查询
		if (studentNo != null && !studentNo.equals("")) {
			sql += " and studentNo like '%" + studentNo + "%'";
		}
		if (studentName != null && !studentName.equals("")) {
			sql += " and studentName like '%" + studentName + "%'";
		}
		if (studentClass != null && !studentClass.equals("")) {
			sql += " and studentClass like '%" + studentClass+"%'";
		}
		if (studentMajor != null && !studentMajor.equals("")) {
			sql += " and studentMajor like '%" + studentMajor+"%'";
		}
		sql += " order by studentNo";
		System.out.println("sql="+sql);
		try {
			conn = ConnectionFactory.getConnection();
			pstmt = conn.createStatement();
			rs = pstmt.executeQuery(sql);
			pa = new Page();// 声明分页类对象
			pa.setPageSize(5);
			pa.setPageCount(rs);
			pa.setCurPage(curPage);
			r = pa.setRs(rs);
			r.previous();
			for (int i = 0; i < pa.getPageSize(); i++) {
				if (rs.next()) {
					student = new Student();
					student.setStudentNo(rs.getString(1));
					student.setStudentName(rs.getString(2));
					student.setStudentClass(rs.getString(3));
					student.setStudentMajor(rs.getString(4));
					student.setPassword(rs.getString(5));
					student.setSumCredit(rs.getFloat(6));
					student.setSumCreditGPA(rs.getFloat(7));
					student.setAvgCreditGPA(rs.getFloat(8));
					student.setMajorRank(rs.getInt(9));
					student.setClassRank(rs.getInt(10));
					list.add(student);
				} else {
					break;
				}
			}
			map = new HashMap();
			map.put("list", list);
			map.put("pa", pa);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
			ResourceClose.close(r, null, null);
		}
		return map;
	}

	@Override
	public void updateScore(String studentNo, float sumCredit, float sumCreditGPA,float avgCreditGPA) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "update Student set sumCredit=?,sumCreditGPA=?,AvgCreditGPA=? where studentNo=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setFloat(1, sumCredit);
			pstmt.setFloat(2, sumCreditGPA);
			pstmt.setFloat(3, avgCreditGPA);
			pstmt.setString(4, studentNo);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
	}
	
	@Override
	public List findAllStudentByMostCon(Student condition) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List findAllStudentByMajor(String major) {
		Student student = null;
		ArrayList list = new ArrayList();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select * from Student where studentMajor=? order by AvgCreditGPA desc";
		try {
			conn = ConnectionFactory.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, major);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				// 将学生记录转化为学生对象
				student = new Student();
				student.setStudentNo(rs.getString(1));
				student.setStudentName(rs.getString(2));
				student.setStudentClass(rs.getString(3));
				student.setStudentMajor(rs.getString(4));
				student.setPassword(rs.getString(5));
				student.setSumCredit(rs.getFloat(6));
				student.setSumCreditGPA(rs.getFloat(7));
				student.setAvgCreditGPA(rs.getFloat(8));
				student.setMajorRank(rs.getInt(9));
				student.setClassRank(rs.getInt(10));
				list.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
		return list;
	}

	@Override
	public List findAllStudentByClass(String cls) {
		Student student = null;
		ArrayList list = new ArrayList();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select * from Student where studentClass=? order by AvgCreditGPA desc";
		try {
			conn = ConnectionFactory.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, cls);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				// 将学生记录转化为学生对象
				student = new Student();
				student.setStudentNo(rs.getString(1));
				student.setStudentName(rs.getString(2));
				student.setStudentClass(rs.getString(3));
				student.setStudentMajor(rs.getString(4));
				student.setPassword(rs.getString(5));
				student.setSumCredit(rs.getFloat(6));
				student.setSumCreditGPA(rs.getFloat(7));
				student.setAvgCreditGPA(rs.getFloat(8));
				student.setMajorRank(rs.getInt(9));
				student.setClassRank(rs.getInt(10));
				list.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
		return list;
	}

	@Override
	public Vector findAllClass() {
		Vector all= new Vector();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql="SELECT DISTINCT studentClass FROM student";
		try {
			conn = ConnectionFactory.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				String studentClass=rs.getString("studentClass");
				all.addElement(studentClass);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
		return all;
	}

	@Override
	public Vector findAllMajor() {
		Vector all= new Vector();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql="SELECT DISTINCT studentMajor FROM student";
		try {
			conn = ConnectionFactory.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				String studentMajor=rs.getString("studentMajor");
				all.addElement(studentMajor);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
		return all;
	}

	@Override
	public void updateClassRank(String studentNo, int classRank) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionFactory.getConnection();
			System.out.println("in updateClassRank studentNo="+studentNo+" and class Rank= "+classRank);
			String sql = "update Student set classRank=? where studentNo=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, classRank);
			pstmt.setString(2, studentNo);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
	}

	@Override
	public void updateMajorRank(String studentNo, int majorRank) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ConnectionFactory.getConnection();
			System.out.println("in updateMajorRank studentNo="+studentNo+" and class Rank= "+majorRank);
			String sql = "update Student set majorRank=? where studentNo=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, majorRank);
			pstmt.setString(2, studentNo);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DaoException("数据库操作异常，请稍后重试!");
		} finally {
			ResourceClose.close(rs, pstmt, conn);
		}
	}

}
